go

exec dbo.p_drop_proc_if_exists 'p_check_principal_exists';

go

--Function of checking principal existence
create procedure p_check_principal_exists(
    --Principal name
    @a_principal_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
    --Searching for the principal
    if not exists(select 1 from meta_principals
		where principal_name = lower(@a_principal_name))
    begin
        raiserror('A principal with the specified name was not found (principal_name = %s) (#error_code = 20047)',
        16, 1, @a_principal_name);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_table_rights';

go

--Function of adding table access rights
create procedure meta_add_table_rights(
    --Table id
    @a_table_id binary(16),
    --Principal name
    @a_principal_name varchar(max),
    --Access type
    @a_access_type numeric(1),
    --Access level
    @a_access_level numeric(1))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_principal_exists @a_principal_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for the same table rights
    if exists(select 1 from meta_table_acl
		where table_id = @a_table_id
		and principal_name = @a_principal_name
		and access_type = @a_access_type)
    begin
        raiserror('A table permission with the same access type was already added (#error_code = 20048)',
        16, 1);
        return;
    end;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_table_acl(table_id, principal_name, access_type, access_level)
    values(@a_table_id, lower(@a_principal_name), @a_access_type, @a_access_level);
    
    if @a_access_type = 1
        exec ('grant select on ' + @a_table_name + ' to ' + @a_principal_name);
    else if @a_access_type = 2
        exec ('grant insert on ' + @a_table_name + ' to ' + @a_principal_name);
    else if @a_access_type = 3
        exec ('grant update on ' + @a_table_name + ' to ' + @a_principal_name);
    else if @a_access_type = 4
        exec ('grant delete on ' + @a_table_name + ' to ' + @a_principal_name);
    
    --Updating principal acl matrix
    exec dbo.p_update_principal_acl_matrix @a_table_id, @a_principal_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_update_principal_acl_matrix';

go

--Function of updating principal acl matrix
create procedure p_update_principal_acl_matrix(
    --Table id
    @a_table_id binary(16),
    --Principal name
    @a_principal_name varchar(26))
as
begin
    --If there is no acl for this principal
    if not exists(select 1 from meta_table_acl
		where table_id = @a_table_id
		and principal_name = lower(@a_principal_name))
        return;

	declare
	--Affected user name
	@a_affected_user_name varchar(26),
	--Affected user cursor
	@a_affected_user_cursor cursor;
	
	set @a_affected_user_cursor = cursor fast_forward read_only for
	select user_name
	from dbo.p_users_affected_by_principal(@a_principal_name);

    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
        exec dbo.p_update_user_acl_matrix @a_table_id, @a_affected_user_name, @a_principal_name;
        
        fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_update_user_acl_matrix';

go

--Function of updating user acl matrix for the specified principal
create procedure p_update_user_acl_matrix(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Principal name
    @a_principal_name varchar(26))
as
begin
    declare
    --Access type
    @a_access_type numeric(1),
    --Access level
    @a_access_level numeric(1),
    --Principal acl cursor
    @a_principal_acl_cursor cursor;
    
    --Reading principal acl
    set @a_principal_acl_cursor = cursor fast_forward read_only for
    select access_type, access_level
    from meta_table_acl
    where table_id = @a_table_id
    and principal_name = lower(@a_principal_name);
    
    open @a_principal_acl_cursor;
    fetch next from @a_principal_acl_cursor into @a_access_type, @a_access_level; 
        
    while @@fetch_status = 0
    begin
        --If it is a full access right
        if @a_access_level = 6
            exec dbo.p_add_full_access_right @a_table_id, @a_user_name, @a_access_type;
        --If it is a own rows access rights
        else if @a_access_level = 1
            exec dbo.p_add_own_rows_access_right @a_table_id, @a_user_name, @a_access_type;
        --If it unit-level access rights
        else
        begin
			declare
			--Affected unit id
			@a_affected_unit_id binary(16),
			--Unit cursor
			@a_unit_cursor cursor;
			
            --Searching for the unit ids for the specified access type
            set @a_unit_cursor = cursor fast_forward read_only for
            select unit_id from dbo.p_get_unit_ids_for_unit_right(@a_user_name, @a_access_level);
            
            open @a_unit_cursor;
            fetch next from @a_unit_cursor into @a_affected_unit_id;
            
            while @@fetch_status = 0
            begin
				exec dbo.p_add_unit_access_right @a_table_id, @a_user_name, @a_affected_unit_id, @a_access_type;
				fetch next from @a_unit_cursor into @a_affected_unit_id;
            end;
            
            close @a_unit_cursor;
            deallocate @a_unit_cursor;
        end;
        
        fetch next from @a_principal_acl_cursor into @a_access_type, @a_access_level;       
    end;
    
    close @a_principal_acl_cursor;
    deallocate @a_principal_acl_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_add_unit_access_right';

go

--Function of adding a unit-level access right
create procedure p_add_unit_access_right(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --The unit id
    @a_unit_id binary(16),
    --Access type
    @a_access_type numeric(1))
as
begin
    --Searching for the access rights
    if not exists(select 1 from meta_table_acl_unit_access
		where table_id = @a_table_id
		and user_name = lower(@a_user_name)
		and unit_id = @a_unit_id
		and access_type = @a_access_type)
        --Inserting the unit-level access right
        insert into meta_table_acl_unit_access
        (table_id, user_name, unit_id, access_type)
        values (@a_table_id, lower(@a_user_name), @a_unit_id, @a_access_type);
end;

go

exec dbo.p_drop_proc_if_exists 'p_add_full_access_right';

go

--Function of adding full access rights
create procedure p_add_full_access_right(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Access type
    @a_access_type numeric(1))
as
begin
    --Searching for the access rights
    if not exists(select 1 from meta_table_acl_full_access
		where table_id = @a_table_id
		and user_name = lower(@a_user_name)
		and access_type = @a_access_type)
        --Inserting access right
        insert into meta_table_acl_full_access (table_id, user_name, access_type)
        values (@a_table_id, lower(@a_user_name), @a_access_type);
end;

go

exec dbo.p_drop_proc_if_exists 'p_add_own_rows_access_right';

go

--Function of adding own rows access right
create procedure p_add_own_rows_access_right(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Access type
    @a_access_type numeric(1))
as
begin
    --Searching for the access rights
    if not exists(select 1 from meta_table_acl_own_rows
		where table_id = @a_table_id
		and user_name = lower(@a_user_name)
		and access_type = @a_access_type)
        --Inserting access right
        insert into meta_table_acl_own_rows (table_id, user_name, access_type)
        values (@a_table_id, lower(@a_user_name), @a_access_type);
end;

go

exec dbo.p_drop_func_if_exists 'p_get_unit_ids_for_unit_right';

go

--Function of getting a list of units affected by the specified user unit-level access right
create function p_get_unit_ids_for_unit_right(
    --User name
    @a_user_name varchar(26),
    --Access level
    @a_access_level numeric(1))
    returns @a_result table(unit_id binary(16))
as
begin
    declare
    --User unit id
    @a_user_unit_id binary(16),
    --User units cursor
    @a_user_units_cursor cursor;
    
    set @a_user_units_cursor = cursor fast_forward read_only for
    select unit_id
    from meta_user_units
    where user_name = lower(@a_user_name);
    
    open @a_user_units_cursor;
    fetch next from @a_user_units_cursor into @a_user_unit_id;
    
    --Enumerating user units
    while @@fetch_status = 0
    begin    
        --If scope is current unit
        if @a_access_level = 2
        begin
            --Adding the current unit id to the list
            insert into @a_result
            values(@a_user_unit_id);
        end;
        --If scope is current and child units
        else if @a_access_level = 3
        begin
			--Adding current and child units to the list
			insert into @a_result
			select unit_id from dbo.p_get_unit_children(@a_user_unit_id);
        end;
        --If scope is current and parent units
        else if @a_access_level = 4
        begin
            --Adding current and parent units to the list
            insert into @a_result
            select unit_id from dbo.p_get_unit_parents(@a_user_unit_id);
        end;
        --If scope is current, parent and child units
        else if @a_access_level = 5
        begin
            --Adding current, parent and child units to the list
            insert into @a_result
            select unit_id from dbo.p_get_unit_parents_children(@a_user_unit_id);
        end;
        
        fetch next from @a_user_units_cursor into @a_user_unit_id;
    end;
    
    close @a_user_units_cursor;
    deallocate @a_user_units_cursor;
    
    return;
end;

go

exec dbo.p_drop_func_if_exists 'p_users_affected_by_principal';

go

--Function of user affected by the principal
create function p_users_affected_by_principal(
    --Principal name
    @a_principal_name varchar(26))
returns @a_result table(user_name varchar(26))
as
begin
	declare
    --Principal type
    @a_principal_type varchar(64);
    
    --Reading principal type
    select @a_principal_type = principal_type
    from meta_principals
    where principal_name = lower(@a_principal_name);
    
    --If it is a user
    if @a_principal_type = 'user'
    begin
		insert into @a_result values(@a_principal_name);
        return;
    end;
    
    --If it is a role
    
    --Inserting current role users
    insert into @a_result
    select user_name
    from meta_user_roles
    where container_role_name = @a_principal_name;
    
    --Inserting child role users
    with cte_child_roles(role_name) as
    (
		select role_name
		from meta_role_roles
		where container_role_name = @a_principal_name
		union all
		select e.role_name
		from meta_role_roles as e
		inner join cte_child_roles as d on e.container_role_name = d.role_name
    )
    insert into @a_result
    select distinct user_name
    from meta_user_roles
    where container_role_name in (select role_name from cte_child_roles)
    and user_name not in (select user_name from @a_result);
    
    return;
end;

go

exec dbo.p_drop_proc_if_exists 'p_delete_user_acl_matrix';

go

--Function of deleting user acl matrix
create procedure p_delete_user_acl_matrix(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Access type
    @a_access_type numeric(1))
as
begin
	--Deleting the unit-level access rights
    delete from meta_table_acl_unit_access
    where table_id = @a_table_id
    and user_name = lower(@a_user_name)
    and access_type = @a_access_type;
    
    --Deleting the full access rights
    delete from meta_table_acl_full_access
    where table_id = @a_table_id
    and user_name = lower(@a_user_name)
    and access_type = @a_access_type;
    
    --Deleting the own row access rights
    delete from meta_table_acl_own_rows
    where table_id = @a_table_id
    and user_name = lower(@a_user_name)
    and access_type = @a_access_type;
end;

go

exec dbo.p_drop_proc_if_exists 'p_refresh_user_acl';

go

--Function of refreshing user acl
create procedure p_refresh_user_acl(
    --User name
    @a_user_name varchar(26))
as
begin
	declare
	--Table id
	@a_table_id binary(16),
	--Table cursor
	@a_table_cursor cursor;
	
	set @a_table_cursor = cursor fast_forward read_only for
	select table_id from meta_tables;
	
	open @a_table_cursor;
	fetch next from @a_table_cursor into @a_table_id;

    while @@fetch_status = 0
    begin
        exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, 1;
        exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, 2;
        exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, 3;
        exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, 4;
        
        fetch next from @a_table_cursor into @a_table_id;
    end;
    
    close @a_table_cursor;
    deallocate @a_table_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_refresh_user_acl_matrix';

go

--Function of refreshing users matrix acl
create procedure p_refresh_user_acl_matrix(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Access type
    @a_access_type numeric(1))
as
begin
    --Deleting old user acl matrix
    exec dbo.p_delete_user_acl_matrix @a_table_id, @a_user_name, @a_access_type;

	declare
	--Principal name
	@a_principal_name varchar(26),
	--Principal cursor
	@a_principal_cursor cursor;
	
	set @a_principal_cursor = cursor fast_forward read_only for
	select principal_name
	from dbo.p_get_user_principals(@a_user_name);
	
    --Getting all user principals
	open @a_principal_cursor;
	fetch next from @a_principal_cursor into @a_principal_name;
    
    --Updating user acl matrix
    while @@fetch_status = 0
    begin
        exec dbo.p_update_principal_acl_matrix @a_table_id, @a_principal_name;
        
        fetch next from @a_principal_cursor into @a_principal_name;
    end;
    
    close @a_principal_cursor;
    deallocate @a_principal_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_table_rights';

go

--Function of deleting table access rights
create procedure meta_delete_table_rights(
    --Table id
    @a_table_id binary(16),
    --Principal name
    @a_principal_name varchar(max),
    --Access type
    @a_access_type numeric(1))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    exec dbo.p_check_principal_exists @a_principal_name, @a_result output;
    
    --Searching for the table rights
    select @a_row_count = count(*)
    from meta_table_acl
    where table_id = @a_table_id
    and principal_name = @a_principal_name
    and access_type = @a_access_type;
    
    if @a_row_count = 0
    begin
        raiserror('Specified table permission was not found (#error_code = 20049)',
        16, 1);
        return;
    end;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    if @a_access_type = 1
        exec ('revoke select on ' + @a_table_name + ' from ' + @a_principal_name);
    else if @a_access_type = 2
        exec ('revoke insert on ' + @a_table_name + ' from ' + @a_principal_name);
    else if @a_access_type = 3
        exec ('revoke update on ' + @a_table_name + ' from ' + @a_principal_name);
    else if @a_access_type = 4
        exec ('revoke delete on ' + @a_table_name + ' from ' + @a_principal_name);
    
    --Getting affected by the principal users
    select user_name into #a_affected_user_list
    from dbo.p_users_affected_by_principal(@a_principal_name);
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    delete from meta_table_acl
    where table_id = @a_table_id
    and principal_name = lower(@a_principal_name)
    and access_type = @a_access_type;
    
    --User name
    declare @a_user_name varchar(26),
    --User cursor
    @a_user_cursor cursor;
    
    --Updating users affected by the principal
    set @a_user_cursor = cursor fast_forward read_only for
    select user_name from #a_affected_user_list;
    
    open @a_user_cursor;
    fetch next from @a_user_cursor into @a_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, @a_access_type;		
		fetch next from @a_user_cursor into @a_user_name;
    end;
    
    close @a_user_cursor;
    deallocate @a_user_cursor;
    
    commit;    
end;